# 该功能是用来将采集服务器上的sql数据导入到117服务器的数据库上
import time

import pymysql
import re

# 这是采集服务器的mysql数据库的连接属性
CJMYSQL_CONNECT = {
    'MYSQL_HOST': '114.115.219.219',

    'MYSQL_PORT': 3306,
    'MYSQL_DB': 'globallegaldatabase',
    'MYSQL_USER': 'root',
    'MYSQL_PASSWD': 'xinanzhengfadaqxueapp03!',
    'MYSQL_CHARSET': 'utf8'
}

# 这是加工服务器的mysql数据库连接属性
BSMYSQL_CONNEXT = {
    'MYSQL_HOST': '114.116.40.73',
    'MYSQL_PORT': 3306,
    'MYSQL_DB': 'southwestpoliticsun',
    'MYSQL_USER': 'lj',
    'MYSQL_PASSWD': 'xinanzhengfadaqxuelj!',
    'MYSQL_CHARSET': 'utf8'
}

# 连接采集数据库
cjhost = CJMYSQL_CONNECT['MYSQL_HOST']
cjport = CJMYSQL_CONNECT['MYSQL_PORT']
cjdb = CJMYSQL_CONNECT['MYSQL_DB']
cjuser = CJMYSQL_CONNECT['MYSQL_USER']
cjpassword = CJMYSQL_CONNECT['MYSQL_PASSWD']
CJMYSQL_TABLE = ['lawtext', 'lawcasetext']  # 这是采集数据库存储数据的表
cjurlconn = pymysql.connect(host=cjhost, port=cjport, db=cjdb, user=cjuser, passwd=cjpassword, charset='utf8')
# 连接加工服务器数据库
bshost = BSMYSQL_CONNEXT['MYSQL_HOST']
bsport = BSMYSQL_CONNEXT['MYSQL_PORT']
bsdb = BSMYSQL_CONNEXT['MYSQL_DB']
bsuser = BSMYSQL_CONNEXT['MYSQL_USER']
bspassword = BSMYSQL_CONNEXT['MYSQL_PASSWD']
BSMYSQL_TABLE = {'lawtext': 'law', 'lawcasetext': 'case'}  # 这是采集数据库存储数据的表
bsurlconn = pymysql.connect(host=bshost, port=bsport, db=bsdb, user=bsuser, passwd=bspassword, charset='utf8')
SQL_COUNTRYS = ['LAWCOUNTRYXJP', 'LAWCOUNTRYFLB', 'LAWCOUNTRYWL', 'LAWCOUNTRYMLXY', 'LAWCOUNTRYTG',
                'LAWCOUNTRYYDNXY', 'LAWCOUNTRYLW', 'LAWCOUNTRYMD', 'LAWCOUNTRYYN', 'LAWCOUNTRYJPZ']

for cjtable in CJMYSQL_TABLE:
    bstable = BSMYSQL_TABLE[cjtable]
    for country in SQL_COUNTRYS:
        # 创建游标
        print("当前处理的是:" + country)
        i = 0
        cjurlcursor = cjurlconn.cursor()
        if cjtable == "lawtext":
            cjsql = "SELECT ID,LegalName,Organizaation,PublishDate,EffectiveDate,SortA,SortB,SortC,SortD,SORTE,SORTF," \
                    "Keyword,SORTG,ChapNo,Articles,Chapter,Section,SYS_FLD_DIGITFILENAME," \
                    "FileUrl,DownLoadUrl,DownLoadWebNameC,SYSID, DownLoadWebNameE, " \
                    "Revisionmark from %s WHERE SortA = '%s'" % (cjtable, country)
        elif cjtable == "lawcasetext":
            if country == "LAWCOUNTRYYDNXY":
                cjsql = "SELECT ID,Title,CaseNumber,KeyWord,People,SortA,CaseOfAction,UseLaw,AdjudicationDate,JudgAgency," \
                        "SortB,SortC,CaseSummary,Articles,Chapter,Section,SYS_FLD_DIGITFILENAME," \
                        "FileUrl,DownLoadUrl,DownLoadWebNameC,DownLoadWebNameE," \
                        "SYSID from %s WHERE SortA = '%s' AND Isconversion = '0'" % (cjtable, country)
            else:
                cjsql = "SELECT ID,Title,CaseNumber,KeyWord,People,SortA,CaseOfAction,UseLaw,AdjudicationDate,JudgAgency," \
                        "SortB,SortC,CaseSummary,Articles,Chapter,Section,SYS_FLD_DIGITFILENAME," \
                        "FileUrl,DownLoadUrl,DownLoadWebNameC,DownLoadWebNameE," \
                        "SYSID from %s WHERE SortA = '%s'" % (cjtable, country)

        try:
            cjurlcursor.execute(cjsql)
            rs = cjurlcursor.fetchall()
            bssysid = []
            bsurlcursor = bsurlconn.cursor()
            bscsql = "SELECT SYSID FROM `%s` WHERE SortA = '%s'" % (bstable, country)
            try:
                bsurlcursor.execute(bscsql)
                bsrs = bsurlcursor.fetchall()
                for bsrsr in bsrs:      # 将查询的sysid更新为列表方便查询
                    file_sql = bsrsr[0]
                    bssysid.append(file_sql)
                for r in rs:
                    SortA = r[5]
                    SYSID = r[21]
                    if SYSID not in bssysid:
                        bsr = list(r)
                        for j in range(len(bsr)):
                            if bsr[j] is None:
                                bsr[j] = ''
                        title = re.sub(r'''"''', r"'", bsr[1])
                        bsr[1] = title
                        if bstable == "law":
                            bsr[7] = ''
                            bsr[10] = ''
                            bssql = '''insert into law (ID,LegalName,Organizaation,PublishDateStr,EffectiveDateStr, 
                            SortA,SortB, SortC, SortD,SORTE,SORTF,Keyword,SORTG,ChapNo,Articles,Chapter,Section, 
                            SYS_FLD_MARKSTATE, SYS_FLD_DIGITFILENAME,FileUrl,DownLoadUrl,DownLoadWebNameC, SYSID, 
                            DownLoadWebNameE) values("%s", "%s","%s","%s","%s","%s","%s","%s",
                            "%s","%s","%s", "%s","%s","%s","%s","%s","%s","%s","%s","%s", "%s","%s","%s",
                            "%s")''' % (
                                bsr[0], bsr[1], bsr[2], bsr[3], bsr[4], bsr[5], bsr[6], bsr[7], bsr[8],
                                bsr[9], bsr[10], bsr[11], bsr[12], bsr[13], bsr[14], bsr[15], bsr[16], 33,
                                bsr[17], bsr[18], bsr[19], bsr[20], bsr[21], bsr[22])
                        elif bstable == "case":
                            bssql = '''insert into `case` (ID,Title,CaseNumber,KeyWord,People,SortA,CaseOfAction, 
                            UseLaw,AdjudicationDateStr,JudgAgency,SortB,SortC,CaseSummary,Articles,Chapter,Section, 
                            SYS_FLD_MARKSTATE,SYS_FLD_DIGITFILENAME, FileUrl,DownLoadUrl,DownLoadWebNameC, 
                            DownLoadWebNameE, SYSID) values("%s", "%s","%s","%s","%s", "%s","%s","%s","%s","%s","%s", 
                            "%s","%s","%s","%s","%s","%s","%s","%s","%s", "%s","%s", "%s")''' % (
                                bsr[0], bsr[1], bsr[2], bsr[3], bsr[4], bsr[5], bsr[6], bsr[7], bsr[8],
                                bsr[9], bsr[10], bsr[11], bsr[12], bsr[13], bsr[14], bsr[15], 33, bsr[16],
                                bsr[17], bsr[18], bsr[19], bsr[20], bsr[21])

                        try:
                            bsurlcursor.execute(bssql)
                            bsurlconn.commit()
                        except Exception as e:
                            print("插入ID=" + str(r[0]) + "数据失败:" + str(e) + '\n')
                    else:
                        print("该条数据在117服务器上已存在:" + str(r[0]) + ' ' + SortA + ' ' + SYSID)
                    i = i + 1
                    print("\r已处理: %d" % i, end="")
            except Exception as e:
                print("117服务器查询出错:" + str(e))
            bsurlcursor.close()
        except Exception as e:
            print("采集服务器查询出错:" + str(e))
        cjurlcursor.close()
bsurlconn.close()
cjurlconn.close()